﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace MechanicsLab
{
    public class LocalDB
    {
        string connStr = "";

        public LocalDB(string path)
        {
            connStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Persist Security Info=False;";
        }

        public DataTable SelLqDataInfo(string name)
        {
            DataTable dt = null;
            try
            {
                string sql = "select trialName,precativeDate,precativeNum,produceCompany,sampleName,model,checkNum,sampleSpace from lq_mp_data where mark='0' and trialName='" + name + "'";
                dt = Query(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public DataTable SelMarkInfo()
        {
            DataTable dt = null;
            try
            {
                string sql = "select top 1 precativeNum,trialName,remark_1 from lq_mp_data where mark='1' order by end_time asc";
                dt = Query(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public DataTable SelPrecativeNum(string precativeNum)
        {
            DataTable dt = null;
            try
            {
                string sql = "select * from lq_mp_data where precativeNum='" + precativeNum + "'";
                dt = Query(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public bool UpdateLqDataInfo(string str)
        {
            bool state = true;
            try
            {
                string sqlstr = str;
                Execute(sqlstr);
            }
            catch (Exception ex)
            {
                state = false;
                throw ex;
            }
            return state;
        }

        public bool AddLqDataInfo(string sqlparm, string sqlvalue)
        {
            bool state = true;
            try
            {
                string sqlstr = "insert into lq_mp_data(" + sqlparm + ") values(" + sqlvalue + ")";
                Execute(sqlstr);
            }
            catch (Exception ex)
            {
                state = false;
                throw ex;
            }
            return state;
        }

        public void RollbackExecute(string[] values)
        {
            OleDbTransaction tran = null;
            OleDbConnection conn = null;
            try
            {
                conn = new OleDbConnection(connStr);
                conn.Open();
                OleDbCommand cmd = new OleDbCommand("", conn);
                tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                foreach (string sql in values)
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public void Execute(string SqlStr)
        {
            OleDbConnection conn = null;
            try
            {
                conn = new OleDbConnection(connStr);
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(SqlStr, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public DataTable Query(string sql)
        {
            OleDbConnection conn = null;
            try
            {
                DataTable table = new DataTable();
                conn = new OleDbConnection(connStr);
                conn.Open();
                using (OleDbDataAdapter da = new OleDbDataAdapter(sql, conn))
                {
                    da.Fill(table);
                }
                return table;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
